﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Diagnostics;

namespace RMS.Utils.DatabaseSchema
{
    class SqlScehamaParser : SchemaParser
    {
        public SqlScehamaParser(SqlConnection connection)
            : base(connection)
        {
        }

        # region [ParseTablesHelperFunction]
        private DataTable GetPKOfTable(string tableName)
        {
            SqlCommand selectCommand = this.Connection.CreateCommand();
            selectCommand.CommandText = "EXEC sp_pkeys '" + tableName + "'";
            selectCommand.Connection = this.Connection;
            SqlDataAdapter adapter = new SqlDataAdapter(selectCommand);
            DataTable dataTable = new DataTable();
            adapter.Fill(dataTable);
            return dataTable;
        }
        private DataTable GetConstrans()
        {
            string selectCommandText = "SELECT    FK_Table  = FK.TABLE_NAME,  FK_Column = CU.COLUMN_NAME,PK_Table  = PK.TABLE_NAME,   PK_Column = PT.COLUMN_NAME, Constraint_Name = C.CONSTRAINT_NAME FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME INNER JOIN   INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME INNER JOIN(SELECT i1.TABLE_NAME, i2.COLUMN_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1 INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME WHERE i1.CONSTRAINT_TYPE ='PRIMARY KEY') PT ON PT.TABLE_NAME = PK.TABLE_NAME";
            DataTable dataTable = new DataTable();
            new SqlDataAdapter(selectCommandText, this.Connection).Fill(dataTable);
            return dataTable;
        }
        private IEnumerable<DataRow> GetFKOfTable(DataTable dtConstrans, string tableName)
        {
            return dtConstrans.AsEnumerable().Where<DataRow>(delegate(DataRow item) { return item.Field<string>("FK_Table") == tableName; }).ToList();
            // return dtConstrans.AsEnumerable().Where(dt => dt["TABLE_NAME"].ToString().Equals(TableName)).ToList();
        }
        private List<string> GetForeignKeyInTable(DataTable dtConstrans, string columnName, string tableName)
        {
            return dtConstrans.AsEnumerable().Where<DataRow>(delegate(DataRow item)
            {
                return item.Field<string>("Pk_COLUMN") == columnName &&
                    item.Field<string>("PK_Table") == tableName;
            }).Select<DataRow, string>(delegate(DataRow item)
            {
                return item.Field<string>("FK_Table");
            }).ToList<string>();
            // return dtConstrans.AsEnumerable().Where(dt => dt["Pk_COLUMN"] == columnName && dt["PK_Table"] = tableName).Select(dt => dt["FK_Table"]).ToList<string>();   
        }
        private DataTable GetIdentityColumns()
        {
            SqlCommand selectCommand = this.Connection.CreateCommand();
            selectCommand.CommandText = "SELECT OBJECT_NAME(id) as ObjectName, Name as IdentityColumn FROM syscolumns WHERE COLUMNPROPERTY( id ,name, 'IsIdentity')=1 or COLUMNPROPERTY( id ,name, 'IsRowGuidCol') = 1 ORDER BY 1, 2";
            selectCommand.Connection = this.Connection;
            SqlDataAdapter adapter = new SqlDataAdapter(selectCommand);
            DataTable dataTable = new DataTable();
            adapter.Fill(dataTable);
            return dataTable;
        }
        private DataRow[] GetIdentityColumnsOfTable(DataTable dtIdentityColumns, string tableName)
        {
            return dtIdentityColumns.Select("ObjectName = '" + tableName + "'");
        }
        private DataTable GetTables()
        {
            DbCommand command = this.Connection.CreateCommand();
            command.CommandText = "Select name as TABLE_NAME from sysobjects where type='U'  and name != 'sysdiagrams' and name !='dtproperties'";
            command.Connection = this.Connection;
            SqlDataAdapter adapter = new SqlDataAdapter((SqlCommand)command);
            DataTable dataTable = new DataTable();
            adapter.Fill(dataTable);
            return dataTable;
        }
        private DataRow[] GetColumnsOfTable(DataTable dtColumns, string tableName)
        {
            return dtColumns.Select("Table_Name = '" + tableName + "'");
        }
        #endregion

        #region [helper]
        public string DefaultValue(string columnName)
        {
            return (Convert.IsDBNull(columnName)) ? null : columnName.ToString();
        }
        public bool IsNullable(string columnName)
        {
            return columnName.ToString().Equals("YES");
        }
        private SqlDbType DatabaseType(string Type)
        {
            try
            {

                return (SqlDbType)Enum.Parse(typeof(SqlDbType), Type, true);
            }
            catch
            {
                return SqlDbType.Variant;
            }
        }
        #endregion

        protected override void ParseTables()
        {
            try
            {
                if (this.Connection.State == ConnectionState.Closed)
                    this.Connection.Open();
                DataTable schema = this.GetTables();
                DataTable dtColumns = this.Connection.GetSchema("Columns");
                foreach (DataRow drTable in schema.Rows)
                {
                    Table table = new Table();
                    table.Name = drTable["TABLE_NAME"].ToString();
                    DataTable dtConstrans = this.GetConstrans();
                    DataRow[] columnsForTableDataRows = this.GetColumnsOfTable(dtColumns, table.Name);
                    foreach (DataRow drColumn in columnsForTableDataRows)
                    {
                        Column column = new Column();
                        column.Name = drColumn["COLUMN_NAME"].ToString();
                        column.DatabaseType = this.DatabaseType(drColumn["DATA_TYPE"].ToString());
                        column.DataTypeLenght = (Convert.IsDBNull(drColumn["CHARACTER_MAXIMUM_LENGTH"])) ? 0 : Convert.ToInt32(drColumn["CHARACTER_MAXIMUM_LENGTH"]); ;
                        column.DefaultValue = this.DefaultValue(drColumn["IS_NULLABLE"].ToString());
                        column.IsNullable = this.IsNullable(drColumn["IS_NULLABLE"].ToString());
                        foreach (DataRow drPK in this.GetPKOfTable(table.Name).Rows)
                        {
                            if (drPK["COLUMN_NAME"].ToString().Equals(column.Name))
                            {
                                column.IsPrimaryKey = true;
                                column.RelatedToForeignKeyInTable = this.GetForeignKeyInTable(dtConstrans, table.Name, column.Name);
                            }
                            foreach (DataRow drIdentity in GetIdentityColumnsOfTable(this.GetIdentityColumns(), table.Name))
                            {
                                if (drIdentity["IdentityColumn"].ToString().Equals(column.Name))
                                {
                                    column.IsIdentity = true;
                                }
                            }
                        }
                        foreach (DataRow drFK in GetFKOfTable(dtConstrans, table.Name))
                        {
                            if (drFK["Fk_COLUMN"].ToString().Equals(column.Name))
                            {
                                column.IsForeignKey = true;
                                column.RelatedToPrimaryKeyInTable = drFK["PK_Table"].ToString();
                            }
                        }
                        table.Columns.Add(column);
                    }
                    base._tables.Add(table);
                }
            }
            finally
            {
                this.Connection.Close();
            }
        }
        protected override void ParseView()
        {
            try
            {
                this.Connection.Open();
                DataTable schema = this.Connection.GetSchema("Views");
                DataTable source = this.Connection.GetSchema("ViewColumns");
                foreach (DataRow row in schema.Rows)
                {
                    View view = new View();
                    view.Name = row.Field<string>("TABLE_NAME");
                    foreach (DataRow row2 in source.AsEnumerable().Where<DataRow>(r => r.Field<string>("VIEW_NAME") == view.Name))
                    {
                        string tableName = row2.Field<string>("TABLE_NAME");
                        string columnName = row2.Field<string>("COLUMN_NAME");
                        Column column = (base.Tables.Single<Table>(e => e.Name == tableName)).Columns.Single<Column>(e => e.Name == columnName);
                        if (!view.Columns.Exists(c => c.Name.ToLower() == column.Name.ToLower()))
                        {
                            view.Columns.Add(column);
                        }
                    }
                    base._views.Add(view);
                }
            }
            finally
            {
                this.Connection.Close();
            }

        }
        public SqlConnection Connection
        {
            get
            {
                return (SqlConnection)base.Connection;
            }
        }
    }
}
